IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.CreateCheckConstraint'))
BEGIN
	DROP PROCEDURE MD3.CreateCheckConstraint
END
GO

CREATE Procedure MD3.CreateCheckConstraint
( 
     @SchemaName		        SYSNAME
    ,@TableName	    	        SYSNAME
    ,@ConstraintName         	SYSNAME
    -- Note: Character string constants must have the single quotes DOUBLED UP. However,
    -- do not use any quotes for columns that are integers. Examples:
    --  @CheckConstraintExpression = 'FooBar IN (''A'',''B'',''C'')'
    --  @CheckConstraintExpression = 'SomeColumn = 0 OR SomeColumn = 1'
    --  @CheckConstraintExpression = 'ColA >= 1 AND ColB <= 20'
    ,@Expression VARCHAR(MAX)
    ,@AllowNoCheck		BIT = 0      -- Allow NOCHECK (ie, existing data may violate the check constraint)
	,@DropConstraint    BIT = 1
)
AS
    
BEGIN

    SET NOCOUNT ON  

    DECLARE @Msg         VARCHAR(MAX) , @DropSQL VARCHAR(MAX), @CreateNoCheckSQL VARCHAR(MAX), @CreateCheckSQL VARCHAR(MAX), @AlterSQL VARCHAR(MAX)
           ,@SQL         VARCHAR(MAX)  
    DECLARE @CurrentConstraintExpression VARCHAR(MAX) , @CurrentConstraintName VARCHAR(MAX), @NewExpression VARCHAR(MAX);
    DECLARE @CurrentIsNotTrustedSetting BIT, @ErrorNumber INT	   


		-- SQL Server optimizes and re-formats the input expression before storing it in the system 
        -- catalog. This includes converting "IN" clauses to "OR" statements and adding parenthesis 
        -- around data values. Etc, etc. 
		
	--prep the DROP and ADD commands
	SELECT @DropSQL =   'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + quotename(@ConstraintName) + ';';
    SELECT @CreateNoCheckSQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' WITH NOCHECK ADD CONSTRAINT ' + quotename(@ConstraintName) + ' CHECK(' + @Expression + ');';
	SELECT @CreateCheckSQL = REPLACE (@CreateNoCheckSQL,'WITH NOCHECK ADD CONSTRAINTT','WITH CHECK ADD CONSTRAINT')
	SELECT @AlterSQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' WITH CHECK CHECK CONSTRAINT ' + quotename(@ConstraintName) + ';';
	
	
	--if we were requested to DROP the constraint, do it.
	IF @DropConstraint = 1
	BEGIN
		IF EXISTS (
			SELECT 1
			FROM MD3.f_GetConstraints (@SchemaName,@TableName)
			WHERE ConstraintName = @ConstraintName
		)
		BEGIN
			EXEC MD3.ExecuteSQL @DropSQL;
		END;

		RETURN 0;
	END;	
		
		
	--Does the constraint exist by definition and not name?
	--If so, rename it. 
	--Constraint names must be unique for all tables in a given schema. 
	SELECT @CurrentConstraintName = ConstraintName
	FROM MD3.f_GetConstraints (@SchemaName,@TableName)
	WHERE ConstraintExpression = @Expression
	AND ConstraintType = 'CHECK'
	AND ConstraintName <> @ConstraintName

	IF @CurrentConstraintName IS NOT NULL
	BEGIN
		EXEC MD3.InsertLogMessage
			@Message = 'The constraint definition already exists, but is a different name.  Renaming...',
			@Severity = 'I';
		SELECT @SQL = 'sp_rename ''' + @SchemaName + '.' + @CurrentConstraintName + ''', ''' + @ConstraintName + ''''
		EXEC MD3.ExecuteSQL @SQL;
		
		--we can't RETURN yet because we haven't checked the trusting yet
	END;		
		
	
	--Does the constraint exist by both definition and name?  (and is fully trusted)
	--If so, RETURN, nothing to do. 
	IF EXISTS (
		SELECT 1
		FROM MD3.f_GetConstraints (@SchemaName,@TableName)
		WHERE ConstraintExpression = @Expression
		AND ConstraintType = 'CHECK'
		AND ConstraintName = @ConstraintName
		AND is_not_trusted = 0		
	)
	BEGIN
		EXEC MD3.InsertLogMessage
			@Message = 'Constraint already exists, and is TRUSTED, no work to do.',
			@Severity = 'I';
		RETURN;
	END;	

	--Does this constraint exist by name but definition *might* be different? (either the expression or trusting)
	--we have to do various things in this case to prep it for possible re-creation.  
    SELECT @CurrentConstraintExpression = ConstraintExpression
          ,@CurrentIsNotTrustedSetting = is_not_trusted
    FROM MD3.f_GetConstraints (@SchemaName,@TableName)
    WHERE ConstraintName = @ConstraintName
	AND ConstraintType = 'CHECK';

    IF (@CurrentConstraintExpression IS NOT NULL)
    BEGIN

        -- Drop it if the trusting is wrong, regardless of Expression matching 
        IF ((@CurrentIsNotTrustedSetting = 1) AND @AllowNoCheck = 0)
        BEGIN
			EXEC MD3.InsertLogMessage
				@Message = 'The constraint exists but is not trusted.  Removing...',
				@Severity = 'I';
			EXEC MD3.ExecuteSQL @DropSQL;
        END;

		--If the Expressions are the same but is_not_trusted is set, let's just try and make it trusted
		--who knows...maybe someone fixed our data quality issues and we can retrust the constraint
		IF ((@CurrentIsNotTrustedSetting = 1) AND (@AllowNoCheck = 1) AND (@CurrentConstraintExpression = @Expression)) 
		BEGIN
			EXEC MD3.InsertLogMessage
				@Message = 'The constraint exists but is not trusted.  This is allowed, but let''s try to fix it if we can',
				@Severity = 'I';
			BEGIN TRY
				EXEC MD3.ExecuteSQL @AlterSQL;
				EXEC MD3.InsertLogMessage
					@Message = 'We were able to successfully TRUST the constraint.',
					@Severity = 'I';
				RETURN;
			END TRY 
			BEGIN CATCH 
				EXEC MD3.InsertLogMessage
					@Message = 'We were NOT able to successfully TRUST the constraint. Someone should investigate.',
					@Severity = 'W';
				RETURN;
			END CATCH 
		END;
		
		--the definitions could be different because of expression re-writing.  We'll check that later and WARN accordingly.  
		IF @CurrentConstraintExpression <> @Expression
		BEGIN
			EXEC MD3.InsertLogMessage
				@Message = 'The constraint name exists, but with a different definition. Removing the old definition.',
				@Severity = 'I';
			EXEC MD3.ExecuteSQL @DropSQL;
		END;
	END;		

	--at this point we can create the constraint as requested.  
	IF @AllowNoCheck = 1
	BEGIN
		--this is important for performance reasons.  
		EXEC MD3.InsertLogMessage
			@Message = 'Creating the constraint. @AllowNoCheck = 1, but we ae going to try to make the constraint TRUSTED anyway.',
			@Severity = 'I';
		BEGIN TRY	
			EXEC MD3.ExecuteSQL @CreateCheckSQL;
		END TRY
		BEGIN CATCH
			--goofy TSQL error handling workaround...documented in that proc.  
			EXEC MD3.LogErrorParse @ErrorNumber = @ErrorNumber OUTPUT;
			
			--547 (The ALTER TABLE statement conflicted with the CHECK constraint).
			IF @ErrorNumber = 547
			BEGIN
				--we failed trusting, so we'll put the constraint on as UNTRUSTED and throw a WARNING
				EXEC MD3.ExecuteSQL @CreateNoCheckSQL;
				EXEC MD3.InsertLogMessage
					@Message = 'We were not able to apply the constraint as TRUSTED.  UNTRUSTED was allowed.  Someone should investigate why there is bad data.',
					@Severity = 'W';
			END
			ELSE
			BEGIN
				SELECT @Msg = 'ErrorNumber ' + convert(varchar(500),@ErrorNumber) + ' occurred during MD3.CreateCheckConstraint.'
				EXEC MD3.InsertLogMessage
					@Message = @Msg,
					@Severity = 'E',
					@ProcedureName = 'MD3.CreateCheckConstraint';
				RAISERROR ('An Error Occurred.  See previous messages or entries in MD3.Log',16,1);
				RETURN 1
			END;
		END CATCH
	END
	ELSE
	BEGIN
		--mandatory trusting
		EXEC MD3.ExecuteSQL @CreateCheckSQL;
	END;


	--by now we know that we've got a "good" constraint.  
	--but we need to make sure that SQL Server didn't rewrite it because our code will then constantly rebuild the constraint needlessly.  
	SELECT @NewExpression = ConstraintExpression
	FROM MD3.f_GetConstraints (@SchemaName,@TableName)
	WHERE ConstraintName = @ConstraintName
   
	--if the new expression is not what was passed, then let's throw a warning for the developer to 
	--fix @Expression so that we are not constantly dropping/adding the same constraint definition
	IF @NewExpression <> @Expression 
	BEGIN
		SELECT @Msg = 'Created constraint: ' + @ConstraintName + ' but the requested @Expression: ' + @Expression + ' does not match'
		SELECT @Msg += ' the actual Expression: ' + @NewExpression + '. You should fix Expression to match the actual:  ' + @NewExpression
		EXEC MD3.InsertLogMessage
			@Message = @Msg,
			@Severity = 'W';
	END;


	
END
GO
